﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Runda.CMS.Common.Entity;
using System.Data.Common;
using System.Data;
using Runda.CMS.Common.Utility;

namespace Runda.CMS.Dal
{
    public partial class WsmAccessoryInfoDal:DalBase
    {
        public List<WsmAccessoryInfoEntity> GetAccessoryEntities(string WhereClause)
        {
            List<WsmAccessoryInfoEntity> list = null;
            string sql = @" 
select B.* from Cms_Wsm_AccessoryInInstrument A 
join Cms_Wsm_AccessoryInfo B on A.AccessoryID = B.AccessoryID
 where 1=1 {0}
";

            sql = string.Format(sql, WhereClause);

            DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);

            using (IDataReader dr = CurrentDatabase.ExecuteReader(dbcommand))
            {
                list = this.GetListFromReader<WsmAccessoryInfoEntity>(dr);
            }

            return list;
        }

        public DataTable GetAccessoryReplacement(string WhereClause)
        {
            string sql = @"select * from (
                select A.*,e1.Name as [PaymentsObjectName],e2.Name as [PaymentTypeName],B.CompanyInstrumentCode,case B.AccessoryType when 1 then '配件' when 2 then '耗材' else '' end as AccessoryType,c.StockSource,c.AccessoryCode from Cms_Wsm_AccessoryReplacement  A 
                JOIN Cms_Wsm_AccessoryInfo B ON A.AccessoryID = B.AccessoryID
                left join Cms_Sys_Dictionary e1 with(nolock) on e1.Type= 5 and e1.ID= A.PaymentsObject
                left join Cms_Sys_Dictionary e2 with(nolock) on e2.Type= 6 and e2.ID= A.PaymentType
                left join Cms_Wsm_AccessoryStock c on c.StockID = a.StockID
                ) T 
                 where 1=1 {0}"; 
            sql = string.Format(sql, WhereClause);
            DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);

            return CurrentDatabase.ExecuteDataSet(dbcommand).Tables[0];
        }

        public DataTable GetAccessortyTable(string WhereClause, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            StringBuilder sqlSb = new StringBuilder(@"select * from (
select A.*,B.CompanyInstrumentCode,B.AccessoryType,C.PaperInvoicesNo,c.RegisterID,CustomerID  from Cms_Wsm_AccessoryReplacement  A JOIN Cms_Wsm_AccessoryInfo B
ON A.AccessoryID = B.AccessoryID
join Cms_Wsm_MaintainBill C on A.MaintainID = C.MaintainID
inner join Cms_Wsm_TaskRegister d on d.RegisterID = c.RegisterID

) AS Z where Deleted=0 {0}");
            string sqlStr = string.Format(sqlSb.ToString(), WhereClause);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {2} ) AS row_number, * 
  FROM ({0}) as A
)
SELECT * from T WHERE row_number > @StartRowNum AND  row_number <= @EndRowNum
select count(*) from ({1}) as B 
";

            CmdText = string.Format(CmdText, sqlStr, sqlStr, OrderBy);

            DbCommand dbCommand = this._db.GetSqlStringCommand(CmdText);
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex - 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, PageIndex * PageSize);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }
    }
}
